<?php

namespace simpleHandle\Component\UtilXls;

use Vtiful\Kernel\Excel;
use Exception;
use Vtiful\Kernel\Format;
use Throwable;
use simpleHandle\Exception\UtilException;
use simpleHandle\Component\UtilTool\Tool;

class Xls
{
	public string $ROOT;
	public const PATH = DIRECTORY_SEPARATOR . 'Static' . DIRECTORY_SEPARATOR;

	public function __construct( $ROOT ) {
		$this->ROOT = $ROOT;
	}

	/**
	 * 导入
	 *
	 * @param string $filePath 文件路径
	 * @param string $sheet    默认Sheet1
	 * @return array
	 * @throws UtilException
	 */
	public function readExcel( string $filePath, string $sheet = "Sheet1" ): array {
		try {
			$filename = basename( $filePath );
			$dirPath  = dirname( $filePath );
			$config   = [ 'path' => $dirPath ];
			$excel    = new Excel( $config );
			$type     = array_fill( 0, 10, Excel::TYPE_STRING );

			return $excel->openFile( $filename )
			             ->openSheet( $sheet, Excel::SKIP_EMPTY_ROW )
			             ->openSheet( $sheet, Excel::SKIP_EMPTY_CELLS )
			             ->setType( $type )
			             ->setSkipRows( 0 )
			             ->getSheetData();
		} catch ( Throwable $th ) {
			throw new UtilException( $th->getMessage(), UtilException::EastXls_ERROR_CODE );
		}
	}

	/**
	 * @param array  $headerData  头部数据
	 * @param array  $contentData 内容数据
	 * @param string $dirPath     存储目录
	 * @return string
	 * @throws UtilException
	 */
	public function normalExport( array $headerData, array $contentData, string $dirPath = 'Default' ): string {
		try {
			self::normalHandle( $headerData, $contentData, $content, $dirPath, $saveName );
			$excelMod      = ( new Excel( [ 'path' => $dirPath ] ) )->fileName( $saveName, 'Sheet1' );
			$fileHandle    = $excelMod->getHandle();
			$formatHeader  = new Format( $fileHandle );
			$formatContent = new Format( $fileHandle );
			// title style
			$titleStyle = $formatHeader->fontSize( 12 )
			                           ->bold()
			                           ->font( "Calibri" )
			                           ->align( Format::FORMAT_ALIGN_CENTER, Format::FORMAT_ALIGN_VERTICAL_CENTER )
			                           ->toResource();
			// global style
			$globalStyle = $formatContent->fontSize( 10 )
			                             ->font( "Calibri" )
			                             ->align( Format::FORMAT_ALIGN_CENTER, Format::FORMAT_ALIGN_VERTICAL_CENTER )
			                             ->border( Format::BORDER_THIN )
			                             ->toResource();
			$headerLen   = count( array_values( $headerData ) );
			$columnEnd   = strtoupper( chr( 65 + $headerLen - 1 ) );
			// column style
			$excelMod->setColumn( "A:{$columnEnd}", 15, $globalStyle );
			// title
			$excelMod->setRow( "A1", 20, $titleStyle );
			// 冻结表头
			$excelMod->freezePanes( 1, 0 );

			return $excelMod->header( array_values( $headerData ) )->data( $content )->output();
		} catch ( Throwable $th ) {
			throw new UtilException( $th->getMessage(), UtilException::EastXls_ERROR_CODE );
		}
	}

	/**
	 * @param array $headerData  头部数据
	 * @param array $contentData 内容数据
	 * @param array $mergeData   合并数据
	 * @param array $styleData   样式
	 * @return string
	 * @throws Exception
	 */
	public function diyExport( array $headerData, array $contentData, array $mergeData, array $styleData = [] ): string {
		try {
			self::normalHandle( $headerData, $contentData, $content, $dirPath, $saveName );
			$excelMod = ( new Excel( [ 'path' => $dirPath ] ) )->fileName( $saveName, 'Sheet1' );
			$excelMod->data( $content );
			$fileHandle = $excelMod->getHandle();
			foreach ( $styleData['column'] ?? [] as $value ) {
				if ( ! isset( $value['range'] ) ) {
					throw new Exception( "no setting range" );
				}
				$formatColSub = new Format( $fileHandle );
				self::formatStyleDiy( $value, $formatColSub );
				$excelMod->setColumn( $value['range'], $value['width'] ?? 15, $formatColSub->toResource() );
			}
			foreach ( $styleData['row'] ?? [] as $value ) {
				if ( ! isset( $value['range'] ) ) {
					throw new Exception( "no setting range" );
				}
				$formatRowSub = new Format( $fileHandle );
				self::formatStyleDiy( $value, $formatRowSub );
				$excelMod->setRow( $value['range'], $value['height'] ?? 20, $formatRowSub->toResource() );
			}
			// 合并数据
			foreach ( $mergeData as $value ) {
				if ( ! isset( $value['range'] ) ) {
					throw new Exception( "no setting range" );
				}
				$formatMergeSub = new Format( $fileHandle );
				self::formatStyleDiy( $value, $formatMergeSub );
				$excelMod->mergeCells( $value['range'], $value['data'] ?? '', $formatMergeSub->toResource() );
			}

			return $excelMod->output();
		} catch ( Throwable $th ) {
			throw new UtilException( $th->getMessage(), UtilException::EastXls_ERROR_CODE );
		}
	}

	/**
	 * 通用处理
	 *
	 * @param array  $headerData  头部数据
	 * @param array  $contentData 内容数据
	 * @param array  $content     返回的数据
	 * @param string $dirPath     返回的路径
	 * @param string $saveName    返回的文件名
	 */
	private function normalHandle( array $headerData, array $contentData, &$content, &$dirPath, &$saveName ) {
		$contentKeys = array_keys( $headerData );
		$content     = [];
		foreach ( $contentData as $value ) {
			$contentSub = [];
			foreach ( $contentKeys as $v ) {
				$contentSub[] = $value[ $v ] ?? '';
			}
			$content[] = $contentSub;
		}
		$dirPath   = self::getSavePath( 'Default' );
		$unionName = self::getFileSaveName();
		$saveName  = $unionName . '.xlsx';
	}

	/**
	 * @param string $dir   目录
	 * @param bool   $isTmp 是否临时
	 * @return bool|string
	 * @throws Exception
	 */
	private function getSavePath( string $dir, bool $isTmp = true ): string {
		if ( $isTmp ) {
			$tmp = ini_get( 'upload_tmp_dir' );
			if ( $tmp !== false && file_exists( $tmp ) ) {
				return realpath( $tmp );
			}

			return realpath( sys_get_temp_dir() );
		}
		$savePath = $this->ROOT . static::PATH . $dir . DIRECTORY_SEPARATOR . date( 'Ymd' ) . DIRECTORY_SEPARATOR;
		if ( ! Tool::createDirectory( $savePath ) ) {
			throw new Exception( "创建目录失败" );
		}

		return $savePath;
	}

	/**
	 * 文件名称 (必须唯一) 规则: sha1(文件名+微秒+百万随机)
	 *
	 * @return string
	 */
	private function getFileSaveName(): string {
		return sha1( uniqid() . microtime( true ) . mt_rand( 100000, 999999 ) );
	}

	/**
	 * 格式化
	 *
	 * @param array  $style  样式数据
	 * @param Format $format 格式对象
	 */
	private function formatStyleDiy( array $style, Format &$format ) {
		if ( isset( $style['font']['size'] ) ) {
			$format->fontSize( $style['font']['size'] );
		}
		if ( isset( $style['font']['font'] ) ) {
			$format->font( $style['font']['font'] );
		}
		if ( isset( $style['font']['bold'] ) ) {
			$format->bold();
		}
		if ( isset( $style['font']['wrap'] ) ) {
			$format->wrap();
		}
		switch ( $style['align'] ?? null ) {
			case 'center_center':
				$format->align( Format::FORMAT_ALIGN_CENTER, Format::FORMAT_ALIGN_VERTICAL_CENTER );
				break;
			case 'left_center':
				$format->align( Format::FORMAT_ALIGN_LEFT, Format::FORMAT_ALIGN_VERTICAL_CENTER );
				break;
			case 'right_center':
				$format->align( Format::FORMAT_ALIGN_RIGHT, Format::FORMAT_ALIGN_VERTICAL_CENTER );
				break;
			default:
				break;
		}
		switch ( $style['border'] ) {
			case 'thin':
				$format->border( Format::BORDER_THIN );
				break;
			case 'dotted':
				$format->border( Format::BORDER_DOTTED );
				break;
				break;
			default:
				break;
		}
	}

	/**
	 * 设置列样式
	 *
	 * @param string      $range
	 * @param int         $width
	 * @param string|null $border
	 * @param string      $align
	 * @param array       $font
	 * @return array
	 */
	public function setColumnStyle(
		string $range,
		int    $width = 15,
		string $border = null,
		string $align = 'center_center',
		array  $font = []
	): array {
		try {
			return [
				'range'  => $range,
				'width'  => $width ?? 15,
				'font'   => [
					'size' => $font['size'] ?? 10,
					'font' => $font['font'] ?? 'Calibri',
					'bold' => $font['bold'] ?? null,
					'wrap' => $font['wrap'] ?? null,
				],
				'align'  => $align ?? null,
				'border' => $border,
			];
		} catch ( Throwable $th ) {
			throw new UtilException( $th->getMessage(), UtilException::EastXls_ERROR_CODE );
		}
	}

	/**
	 * 设置行样式
	 *
	 * @param string $range
	 * @param int    $height
	 * @param array  $font
	 * @param string $align
	 * @param null   $border
	 * @return array
	 * @throws UtilException
	 */
	public function setRowStyle( string $range, int $height = 15, $border = null, $align = 'center_center', $font = [] ): array {
		try {
			return [
				'range'  => $range,
				'height' => $height ?? 15,
				'font'   => [
					'size' => $font['size'] ?? 10,
					'font' => $font['font'] ?? 'Calibri',
					'bold' => $font['bold'] ?? null,
					'wrap' => $font['wrap'] ?? null,
				],
				'align'  => $align ?? null,
				'border' => $border,
			];
		} catch ( Throwable $th ) {
			throw new UtilException( $th->getMessage(), UtilException::EastXls_ERROR_CODE );
		}
	}

	/**
	 * 设置合并样式
	 *
	 * @param string      $range
	 * @param string      $data
	 * @param string|null $border
	 * @param string      $align
	 * @param array       $font
	 * @return array
	 * @throws UtilException
	 */
	public function setMergeStyle( string $range, string $data, string $border = null, string $align = 'center_center', array $font = [] ): array {
		try {
			return [
				'range'  => $range,
				'data'   => $data,
				'font'   => [
					'size' => $font['size'] ?? 10,
					'font' => $font['font'] ?? 'Calibri',
					'bold' => $font['bold'] ?? null,
					'wrap' => $font['wrap'] ?? null,
				],
				'align'  => $align ?? null,
				'border' => $border,
			];
		} catch ( Throwable $th ) {
			throw new UtilException( $th->getMessage(), UtilException::EastXls_ERROR_CODE );
		}
	}

	/**
	 * 数字转字母 （Excel列标）
	 *
	 * @param int $index 索引值
	 * @param int $start 字母起始值
	 * @return String 返回字母
	 * @throws UtilException
	 */
	public function IntToChr( int $index, int $start = 65 ): string {
		try {
			$str = '';
			if ( floor( $index / 26 ) > 0 ) {
				$str .= self::IntToChr( floor( $index / 26 ) - 1 );
			}

			return $str . chr( $index % 26 + $start );
		} catch ( Throwable $th ) {
			throw new UtilException( $th->getMessage(), UtilException::EastXls_ERROR_CODE );
		}
	}
}